﻿/*
 * File: AdventureDatabase.cs (original name: Db.cs)
 * Developer : Brent Larsen (brentoboy@gmail.com)
 * Date: 2011-AUG-9
 * Copyright (c) 2011 Brent Larsen.
 * Distributed under the terms of the GPL v3
 *  
 * This file is part of GenX-RPG
 * http://code.google.com/p/genx-rpg/ 
 * 
 * GenX-RPG is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * GenX-RPG is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with GenX-RPG.  If not, see <http://www.gnu.org/licenses/>.
 */

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;

namespace Rpg
{
    public class AdventureDatabase : IDisposable
    {
        public static string GenxRpgDataFolder 
        { 
            get { return Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\GenX-RPG"; } 
        }

        public static string AdventuresParentFolder 
        { 
            get { return Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\GenX-RPG\\Adventures"; } 
        }

        public static string PathToAdventureFolder(string adventureFolder) 
        { 
            return "{0}\\{1}".f(AdventuresParentFolder, adventureFolder); 
        }

        public static string PathToAdventureDataFile(string adventureFolder) 
        { 
            return "{0}\\{1}\\data".f(AdventuresParentFolder, adventureFolder); 
        }

        public string AdventureFolder { get; private set; }

        private const string ConnectionStringTemplate = "Data Source={0};Version=3;";
        private SQLiteConnection _db;

        public static void __createAppDataFolder()
        {
            var folder = GenxRpgDataFolder;
            if (!Directory.Exists(folder))
                Directory.CreateDirectory(folder);

            folder = AdventuresParentFolder;
            if (!Directory.Exists(folder))
                Directory.CreateDirectory(folder);
        }


        public static Dictionary<string, string> GetAdventureList()
        {
            var AdventureList = new Dictionary<string, string>();

            __createAppDataFolder();
            var adventureFolders = Directory.GetDirectories(AdventuresParentFolder);

            foreach (var adventureFolder in adventureFolders)
            {
                var dir = new DirectoryInfo(adventureFolder);
                using (var db = new AdventureDatabase(dir.Name))
                {
                    AdventureList.Add(dir.Name, db.GetAdventureName());
                }
            }

            return AdventureList;
        }

        public AdventureDatabase(string adventureFolder)
        {
            AdventureFolder = adventureFolder;

            var dbPath = PathToAdventureDataFile(adventureFolder);
            Contract.Requires(File.Exists(dbPath));

            _db = new SQLiteConnection(ConnectionStringTemplate.f(dbPath));
            _db.Open();
        }

        public AdventureDatabase(string adventureFolder, string name)
        {
            AdventureFolder = adventureFolder;
            var folderPath = PathToAdventureFolder(adventureFolder);
            Contract.Requires(!Directory.Exists(folderPath));
            Directory.CreateDirectory(folderPath);

            var dbPath = PathToAdventureDataFile(adventureFolder);
            Contract.Requires(!File.Exists(dbPath));

            _db = new SQLiteConnection(ConnectionStringTemplate.f(dbPath));
            _db.Open();

            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                _db.Execute(Properties.Resources.CreateAdventure);

                var rowsAffected = _db.Execute(
                    "update adventure set name = {0};"
                    , _db.EncodeText(name)
                    );
                Contract.Asserts(rowsAffected == 1);

                transaction.Commit();
            }
        }

        public void UpdateAdventure(Adventure adventure)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                var rowsAffected = _db.Execute(
                    "update adventure set name = {0}" +
                    ", copyright_owner = {1}, date_originally_published = {2}, copyright_notice = {3}, license = (4) " +
                    ", start_map_id = {5}, start_x = {6}, start_y = {7};"
                    , _db.EncodeText(adventure.Name)
                    , _db.EncodeText(adventure.CopyrightOwner)
                    , _db.EncodeText(adventure.DateOriginallyPublished)
                    , _db.EncodeText(adventure.CopyrightNotice)
                    , _db.EncodeText(adventure.License)
                    , adventure.StartLocation.MapId
                    , adventure.StartLocation.X
                    , adventure.StartLocation.Y
                    );
                Contract.Asserts(rowsAffected == 1);

                transaction.Commit();
            }
        }

        public Adventure GetAdventureInfo()
        {
            using (var reader = _db.Query(
                "select name, copyright_owner, date_originally_published, copyright_notice, license, start_map_id, start_x, start_y " +
                "from adventure;"))
            {
                Contract.Asserts(reader.Read());

                var adventure = new Adventure() 
                { 
                    Name = reader.GetString(0),
                    CopyrightOwner = reader.GetString(1),
                    DateOriginallyPublished = reader.GetString(2),
                    CopyrightNotice = reader.GetString(3),
                    License = reader.GetString(4),
                };

                adventure.StartLocation.MapId = reader.GetInt32(5);
                adventure.StartLocation.X = reader.GetInt32(6);
                adventure.StartLocation.Y = reader.GetInt32(7);

                Contract.Asserts(!reader.Read()); // there should not be more than one row

                return adventure;
            }
        }


        public string GetAdventureName()
        {
            return _db.Get("select name from adventure;").ToString();
        }

        public Dictionary<int, string> GetSavedGameNames()
        {
            var savedGames = new Dictionary<int, string>();

            using (var reader = _db.Query("select id, name from saved_game;"))
            {
                while (reader.Read())
                {
                    savedGames.Add(reader.GetInt32(0), reader.GetString(1));
                }
            }

            return savedGames;
        }

        public string GetSavedGameName(int id)
        {
            return _db.Get("select name from saved_game where id = {0};", id).ToString();
        }

        public int StartGame(string name)
        {
            int id;
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                id = (int)(long)_db.Get(
                    "insert into saved_game(name, map_id, x, y) " +
                    "select {0}, start_map_id, start_x, start_y from adventure limit 1;" +
                    "select last_insert_rowid();"
                    , _db.EncodeText(name)
                    );

                transaction.Commit();
            }
            return id;
        }

        public void UpdateSavedGame(int id, string name)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                var rowsAffected = _db.Execute(
                    "update saved_game set name = {0} where id = {1};"
                    , _db.EncodeText(name)
                    , id
                    );

                Contract.Asserts(rowsAffected == 1);

                transaction.Commit();
            }
        }

        public void DeleteSavedGame(int id)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                _db.Execute(
                    "delete from saved_game_step where saved_game_id = {0};" +
                    "delete from saved_game where id = {0};"
                    , id
                    );

                transaction.Commit();
            }
        }

        public Location GetCurrentLocation(int gameId)
        {
            using (var reader = _db.Query(
                "select map_id, x, y from saved_game where id = {0};"
                , gameId
                ))
            {
                Contract.Asserts(reader.Read());

                var location = new Location()
                {
                    MapId = reader.GetInt32(0),
                    X = reader.GetInt32(1),
                    Y = reader.GetInt32(2)
                };

                Contract.Asserts(!reader.Read()); // there should not be more than one row

                return location;
            }
        }

        public void SetCurrentLocation(int gameId, int mapId, int x, int y)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                var rowAffected = _db.Execute(
                    "update saved_game set map_id = {1}, x = {2}, y = {3} where id = {0};" + 
                    "update saved_game_step set state = 1 where (state = 0) and (saved_game_id = {0}) and (step_id = {1});" +
                    "update saved_game_step set state = 1 where (state = 0) and (saved_game_id = {0}) and (step_id = (select id from cell where (map_id = {1}) and (x = {2}) and (y = {3})));"
                    , gameId
                    , mapId
                    , x
                    , y
                    );

                transaction.Commit();
            }
        }

        public void SetPortalState(int gameId, int portalId, int state)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                var rowAffected = _db.Execute(
                    "update saved_game_step set state = {2} where (state < {2}) and (saved_game_id = {0}) and (step_id = {1});"
                    , gameId
                    , portalId
                    , state
                    );

                transaction.Commit();
            }
        }

        public Dictionary<int, string> GetMapNames()
        {
            using (var reader = _db.Query("select id, name from map"))
            {
                var dict = new Dictionary<int, string>();

                while (reader.Read())
                {
                    dict.Add(reader.GetInt32(0), reader.GetString(1));
                }

                return dict;
            }
        }

        public Map LoadMap(int id, int? savedGameId)
        {
            Map map;

            using (var reader = _db.Query("select id, name, width, height from map where id = {0};", id))
            {
                Contract.Asserts(reader.Read());

                map = new Map(
                    id: reader.GetInt32(0),
                    name: reader.GetString(1),
                    width: reader.GetInt32(2),
                    height: reader.GetInt32(3)
                    );

                Contract.Asserts(!reader.Read()); // there should not be more than one row
            }

            using (var reader = _db.Query(
                "select distinct t.bmp_name " +
                "from tile t " +
                "join cell c on c.tile_id = t.id " +
                "join map m on m.id = c.map_id " +
                "where m.id = {0};"
                , id))
            {
                while (reader.Read())
                {
                    map.TileBitmapNames.Add(reader.GetString(0));
                }
            }

            if (savedGameId.HasValue)
            {
                using (var reader = _db.Query(
                    "select id" +
                    ", x" +
                    ", y" +
                    ", tile_id" +
                    ", walk" +
                    ", coalesce((select state from saved_game_step st where st.step_id = c.id and st.saved_game_id = {1}), 0) as state " +
                    "from cell c " +
                    "where map_id = {0}"
                    , id
                    , savedGameId.Value
                    ))
                {
                    Contract.Asserts(reader.HasRows);

                    while (reader.Read())
                    {
                        var cell = new MapCell(
                            id: reader.GetInt32(0), 
                            mapId: id,
                            x: reader.GetInt32(1),
                            y: reader.GetInt32(2), 
                            tileId: reader.GetInt32(3),
                            walk: reader.GetInt32(4),
                            state: reader.GetInt32(5)
                            );

                        map.Cells[cell.X, cell.Y] = cell;
                    }
                }

                using (var reader = _db.Query(
                    "select id, x1, y1, x2, y2" +
                    ", to_map_id, to_x1, to_y1, to_x2, to_y2" +
                    ", coalesce((select state from saved_game_step st where st.step_id = p.id and st.saved_game_id = {1}), 0) as state " +
                    "from portal p " +
                    "where map_id={0} or to_map_id={0};"
                    , id
                    , savedGameId
                    ))
                {
                    while (reader.Read())
                    {
                        var portal = new Portal(
                            id: reader.GetInt32(0),
                            mapId: id,
                            x1: reader.GetInt32(1),
                            y1: reader.GetInt32(2),
                            x2: reader.GetInt32(3),
                            y2: reader.GetInt32(4),
                            to_mapId: reader.GetInt32(5),
                            to_x1: reader.GetInt32(6),
                            to_y1: reader.GetInt32(7),
                            to_x2: reader.GetInt32(8),
                            to_y2: reader.GetInt32(9),
                            state: reader.GetInt32(10)
                            );

                        if (portal.MapId == map.Id)
                            map.Portals.Add(portal);

                        if (portal.To_MapId == map.Id)
                            map.AntiPortals.Add(portal);
                    }
                }
            }
            else
            {
                using (var reader = _db.Query("select id, x, y, tile_id, walk from cell where map_id = {0};", id))
                {
                    Contract.Asserts(reader.HasRows);

                    while (reader.Read())
                    {
                        var cell = new MapCell(
                            id: reader.GetInt32(0),
                            mapId: id,
                            x: reader.GetInt32(1),
                            y: reader.GetInt32(2),
                            tileId: reader.GetInt32(3),
                            walk: reader.GetInt32(4),
                            state: 0
                            );

                        map.Cells[cell.X, cell.Y] = cell;
                    }
                }

                using (var reader = _db.Query(
                    "select id, x1, y1, x2, y2, to_map_id, to_x1, to_y1, to_x2, to_y2 from portal where map_id = {0};"
                    , id
                    ))
                {
                    while (reader.Read())
                    {
                        var portal = new Portal(
                            id: reader.GetInt32(0),
                            mapId: id,
                            x1: reader.GetInt32(1),
                            y1: reader.GetInt32(2),
                            x2: reader.GetInt32(3),
                            y2: reader.GetInt32(4),
                            to_mapId: reader.GetInt32(5),
                            to_x1: reader.GetInt32(6),
                            to_y1: reader.GetInt32(7),
                            to_x2: reader.GetInt32(8),
                            to_y2: reader.GetInt32(9),
                            state: 0
                            );

                        if (portal.MapId == map.Id)
                            map.Portals.Add(portal);

                        if (portal.To_MapId == map.Id)
                            map.AntiPortals.Add(portal);
                    }
                }
            }

            return map;
        }

        public void SaveMap(Map _map)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                if (_map.Id > 0)
                {
                    // update db row
                    var rowAffected = _db.Execute(
                        "update map set name = {0}, width = {1}, height = {2} where id = {3};"
                        , _db.EncodeText(_map.Name)
                        , _map.Width
                        , _map.Height
                        , _map.Id
                        );
                    Contract.Asserts(rowAffected == 1);

                    // kill the cells that no longer fit
                    _db.Execute(
                        "delete from cell where map_id = {0} and (x >= {1} or y >= {2});"
                        , _map.Id
                        , _map.Width
                        , _map.Height
                        );
                }
                else
                {
                    // insert db row, and update the mapid
                    _map.Id = (int)(long)_db.Get(
                        "insert into step(type) values('map');" +
                        "select last_insert_rowid();"
                        );
                    Contract.Asserts(_map.Id > 0);

                    _db.Execute(
                        "insert into map(id, name, width, height) values ({0}, {1}, {2}, {3});"
                        , _map.Id
                        , _db.EncodeText(_map.Name)
                        , _map.Width
                        , _map.Height
                        );
                }

                foreach (var cell in _map.Cells)
                {
                    if (cell.Id > 0)
                    {
                        Contract.Asserts(cell.MapId == _map.Id);

                        // update db row
                        var rowAffected = _db.Execute(
                            "update cell set tile_id = {1}, walk = {2} where id = {0};"
                            , cell.Id
                            , cell.TileId
                            , cell.Walk
                            );
                        Contract.Asserts(rowAffected == 1);
                    }
                    else if (cell.Id == 0)
                    {
                        // fix the mapid to be the right thing and call an insert statement, then update the cell id.
                        cell.MapId = _map.Id;

                        cell.Id = (int)(long)_db.Get(
                            "insert into step(type) values('cell');" +
                            "select last_insert_rowid();"
                            );
                        Contract.Asserts(cell.Id > 0);

                        var rowsAffected = _db.Execute(
                            "insert into cell(id, map_id, x, y, tile_id, walk) values ({0}, {1}, {2}, {3}, {4}, {5});"
                            , cell.Id
                            , cell.MapId
                            , cell.X
                            , cell.Y
                            , cell.TileId
                            , cell.Walk
                            );
                        Contract.Asserts(rowsAffected == 1);
                    }
                    else
                    {
                        Contract.UnexpectedCase();
                    }
                }

                foreach (var portal in _map.Portals.Union(_map.AntiPortals))
                {
                    if (portal.Id > 0)
                    {
                        Contract.Asserts(portal.MapId == _map.Id);

                        if ((portal.MapId == _map.Id && (portal.X1 < _map.Width && portal.Y1 < _map.Height))
                            || (portal.To_MapId == _map.Id && (portal.To_X1 < _map.Width && portal.To_Y1 < _map.Height)))
                        {
                            // update db row
                            var rowAffected = _db.Execute(
                                "update portal set x1 = {0}, y1 = {1}, x2 = {2}, y2 = {3}" +
                                ", to_map_id = {4}, to_x1 = {5}, to_y1 = {6}, to_x2 = {7}, to_y2 = {8} " +
                                "where id = {9};"
                                , portal.X1
                                , portal.Y1
                                , portal.X2
                                , portal.Y2
                                , portal.To_MapId
                                , portal.To_X1
                                , portal.To_Y1
                                , portal.To_X2
                                , portal.To_Y2
                                , portal.Id
                                );
                            Contract.Asserts(rowAffected == 1);
                        }
                        else
                        {  // this map has been resized and this portal is not accessable anymore
                            var rowAffected = _db.Execute(
                                "delete from saved_game_step where step_id = {0};" +
                                "delete from portal where id = {0};" +
                                "delete from step where id = {0};"
                                , portal.Id
                                );
                            // this assertion is about the portal not the step or saved game steps, and it is more of a notification interest than it is important, so I am just not checking it rather than make harry logic just to make sure things stayed clean that dont really have a bad after effect if they didnt.
                            // Contract.Asserts(rowAffected == 1);
                        }
                    }
                    else if (portal.Id == 0)
                    {
                        // fix the mapid to be the right thing and call an insert statement, then update the portal id.
                        portal.MapId = _map.Id;

                        portal.Id = (int)(long)_db.Get(
                            "insert into step(type) values('portal');"+
                            "select last_insert_rowid();"
                            );

                        Contract.Asserts(portal.Id > 0);

                        var rowsAffected = _db.Execute(
                            "insert into portal(id, map_id, x1, y1, x2, y2, to_map_id, to_x1, to_y1, to_x2, to_y2) " +
                            "values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10});"
                            , portal.Id
                            , portal.MapId
                            , portal.X1
                            , portal.Y1
                            , portal.X2
                            , portal.Y2
                            , portal.To_MapId
                            , portal.To_X1
                            , portal.To_Y1
                            , portal.To_X2
                            , portal.To_Y2
                            );
                        Contract.Asserts(rowsAffected == 1);
                    }
                    else
                    {
                        Contract.UnexpectedCase();
                    }
                }

                transaction.Commit();
            }
        }

        public void DeleteMap(int id)
        {
            Contract.Assumes(id != 0);

            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                _db.Execute(
                    "delete from saved_game_step where step_id in (select id from cell where map_id = {0});" +
                    "delete from cell where map_id = {0};" +
                    "delete from step where type='cell' and not exists (select id from cell where id = step.id);" +

                    "delete from saved_game_step where step_id in (select id from portal where map_id = {0});" +
                    "delete from portal where map_id = {0};" +
                    "delete from step where type='portal' and not exists (select id from portal where id = step.id);" +

                    "delete from saved_game_step where step_id = {0};" +
                    "delete from map where id = {0};" +
                    "delete from step where id = {0};"
                    , id
                    );

                transaction.Commit();
            }
        }

        public void DeletePortal(int id)
        {
            using (SQLiteTransaction transaction = _db.BeginTransaction())
            {
                _db.Execute(
                    "delete from saved_game_step where step_id = {0};" +
                    "delete from portal where id = {0};" +
                    "delete from step where id = {0};"
                    , id
                    );

                transaction.Commit();
            }
        }

        public void Dispose()
        {
            _db.Close();
            _db = null;
        }
    }
}
